package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.aop.Before;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.dao.Category;
import cn.com.dashihui.web.dao.DeatiImage;
import cn.com.dashihui.web.dao.DetailsImg;
import cn.com.dashihui.web.dao.GoodsBaseImages;
import cn.com.dashihui.web.dao.GoodsEcard;
import cn.com.dashihui.web.dao.GoodsItem;
import cn.com.dashihui.web.dao.GoodsSaleGT;
import cn.com.dashihui.web.dao.GoodsSaleItem;
import cn.com.dashihui.web.dao.GoodsSaleOption;
import cn.com.dashihui.web.dao.GoodsSaleSpec;
import cn.com.dashihui.web.dao.GoodsSaleTemplate;

public class EcardGoodsService {
	
	/**
	 * 查找出所有商品
	 * 2015-10-26
	 */
	public List<GoodsEcard> findAllGoodsStore(){
		return GoodsEcard.me().find("SELECT * FROM t_bus_goods_ecard ORDER BY id ASC");
	}
	/**
	 * 分页查找商品
	 * @param pageNum
	 * @param pageSize
	 */
	public Page<Record> findByPage(int pageNum,int pageSize,int sGoodId,String sSellerId,String sGoodName,String sStoreId,int state){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_goods_ecard AS A "
				+ "INNER JOIN t_bus_goods_store_rel F ON A.good_store = F.goodstore "
				+ "left JOIN t_dict_store AS C ON F.storeid= C.storeid "
				+ "left JOIN t_bus_seller AS B ON B.sellerId= C.sellerId "
				+ "left join t_bus_goods_title D on D.titleCode=A.titleCord "
				+ "left join t_bus_goods_toker T on T.goodsId=A.id "
				+ "LEFT JOIN t_dict_category E on A.categoryonid=E.categoryId where 1=1");
		List<Object> params = new ArrayList<Object>();
		if(sGoodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(sGoodId);
		}
		if (StrKit.notBlank(sGoodName)) {
			sBuffer.append(" AND A.name like ?");
			params.add("%"+sGoodName+"%");
		}
		if(!StrKit.isBlank(sSellerId)&& !"0".equals(sSellerId)){
			sBuffer.append(" AND B.sellerId in ('"+sSellerId+"')");
		}
		if(!StrKit.isBlank(sStoreId)&&!"0".equals(sStoreId)){
			sBuffer.append(" AND F.storeid in ('"+sStoreId+"')");
		}
		switch (state) {
			case 1:
				sBuffer.append(" AND A.pstate=1 and A.state=1");
				break;
			case 2:
				sBuffer.append(" AND A.pstate=1 and A.state=2");
				break;
			case 3:
				sBuffer.append(" AND A.pstate=1 and A.state=3");
				break;
			case 4:
				sBuffer.append(" AND A.pstate=2");
				break;
			case 5:
				sBuffer.append(" AND A.pstate=3");
				break;
			case 6:
				sBuffer.append(" AND A.pstate=1 and A.state=4");
				break;
		}

		sBuffer.append(" GROUP BY A.id ORDER BY A.createDate DESC");
		return Db.paginate(pageNum, pageSize, "SELECT A.*,B.sellerName,D.title,E.categoryName as oneName ,T.state as tState ", sBuffer.toString(), params.toArray());
	}
	/**
	 * 查找商品
	 */

	public GoodsEcard findById(int id){
		return GoodsEcard.me().findFirst("SELECT A.*,B.sellerName,D.title FROM t_bus_goods_ecard AS A INNER JOIN t_bus_goods_store_rel F ON A.good_store = F.goodstore "
				+ "inner JOIN t_dict_store AS C ON F.storeid= C.storeid inner JOIN t_bus_seller AS B ON B.sellerId= C.sellerId "
				+ " left join t_bus_goods_title D on D.titleCode=A.titleCord where A.id=?",id);
	}
	public List<DeatiImage> findDetailAllImages(String LunBoNum) {
		return DeatiImage.me().find("SELECT A.* FROM goods_details_images A WHERE A.LunBoNum=? ORDER BY A.orderNo",LunBoNum);
	}
	public int doCancel(String id,String reason, String checktime) {
		return Db.update("update t_bus_goods_ecard set state=4 ,checktime=?,reason=? where id=?",checktime,reason,id);
	}
	public int doAccept(String id, String checktime) {
		return Db.update("update t_bus_goods_ecard set state=2 ,checktime=?,uptime=? where id=?",checktime,checktime,id);
	}
	public boolean delete(GoodsEcard goods) {
		return goods.delete();
	}
	public int tooDown(String id, String checktime) {
		return Db.update("update t_bus_goods_ecard set state=5 ,checktime=? where id=?",checktime,id);
	}
	public int doDown(String id, String downtime) {
		return Db.update("update t_bus_goods_ecard set state=3 ,pstate=1,downtime=? WHERE id=?",downtime,id);
	}
	public int doUp( String id,String createDate ) {
		return Db.update("update t_bus_goods_ecard set state=1 ,pstate=1 ,createDate=? WHERE id=?",createDate,id);
	}
	public Record findGoodDetailById(int id) {
			return Db.findFirst("select * from t_bus_goods_ecard where id=?",id);
	}
	public List<Record> getGoodsItem(String gooditem) {
		return Db.find("select * from t_bus_goods_item where goodItem=?",gooditem);
	}
	public List<Record> getStore(String good_store) {
		return Db.find("SELECT A.title,A.address,A.tel,A.title FROM t_dict_store A WHERE A.storeid IN (select storeid from t_bus_goods_store_rel where goodstore=?) and A.qstate=1 and A.state=2 ",good_store);
	}
	
	/**
	 * 商品规格
	 * @param gooditem
	 * @return
	 */
	public List<Record> getGoodSpec(int goodsId) {
		return Db.find("select * from t_bus_goods_spec where goodId=?",goodsId);
	}
	
	public List<Record> findExcelByPage(int sGoodId, String sSellerId,String sGoodName, String sStoreId, int state) {
		StringBuffer sBuffer = new StringBuffer(" SELECT A.*,B.sellerName,E.categoryName as oneName,T.state as tState FROM t_bus_goods_ecard AS A INNER JOIN t_bus_goods_store_rel F ON A.good_store = F.goodstore inner JOIN t_dict_store AS C ON F.storeid= C.storeid inner JOIN t_bus_seller AS B ON B.sellerId= C.sellerId left join t_bus_goods_toker T on T.goodsId=A.id LEFT JOIN t_dict_category E on A.categoryonid=E.categoryId where 1=1");
		List<Object> params = new ArrayList<Object>();
		if(sGoodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(sGoodId);
		}
		if (StrKit.notBlank(sGoodName)) {
			sBuffer.append(" AND A.name like ?");
			params.add("%"+sGoodName+"%");
		}
		if(!StrKit.isBlank(sSellerId)&& !"0".equals(sSellerId)){
			sBuffer.append(" AND B.sellerId in ('"+sSellerId+"')");
		}
		if(!StrKit.isBlank(sStoreId)&&!"0".equals(sStoreId)){
			sBuffer.append(" AND F.storeid in ('"+sStoreId+"')");
		}
		switch (state) {
			case 1:
				sBuffer.append(" AND A.pstate=1 and A.state=1");
				break;
			case 2:
				sBuffer.append(" AND A.pstate=1 and A.state=2");
				break;
			case 3:
				sBuffer.append(" AND A.pstate=1 and A.state=3");
				break;
			case 4:
				sBuffer.append(" AND A.pstate=2");
				break;
			case 5:
				sBuffer.append(" AND A.pstate=3");
				break;
		}
		
		sBuffer.append(" GROUP BY A.id ORDER BY A.createDate DESC");
		return Db.find( sBuffer.toString(), params.toArray());
	}
	 
	/**
	 * 导出数据
	 * @param response
	 * @param request
	 * @param listz 
	 * @param titles2 
	 * @param member
	 */
	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "商品上架列表", titles, listz);
	}
	/**
	 * 查询所有的一级category
	 * @param iid
	 * @return
	 */
	public List<Category> finOneCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=1");
	}
	/**
	 * 查询所有的二级category
	 * @param iid
	 * @return
	 */
	public List<Category> findTwoCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=2");
	}
	/**
	 * 查询所有的三级category
	 * @param iid
	 * @return
	 */
	public List<Category> findThreeCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=3");
	}
	/**
	 * 查询所有的四级category
	 * @param iid
	 * @return
	 */
	public List<Category> findFourCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=4");
	}
	public int deteleItem(String goodItem) {
		return Db.update("DELETE FROM t_bus_goods_item WHERE goodItem=?",goodItem);
	}
	public int detelepayType(int id) {
		return Db.update("update t_bus_goods_ecard set sellpoint=0,sellPrice=0  WHERE id=?",id);
	}
	public int detelestore(String goodstore) {
		return Db.update("DELETE FROM t_bus_goods_store_rel WHERE goodstore=?",goodstore);
	}
	public int detelezdrs(int id) {
		return Db.update("update t_bus_goods_ecard set zdrs=0 WHERE id=?",id);
	}
	public int detelejyrs(int id) {
		return Db.update("update t_bus_goods_ecard set jyrs=0 WHERE id=?",id);
	}
	public GoodsEcard findByIdGoods(int id){
		return GoodsEcard.me().findFirst("SELECT * FROM  t_bus_goods_ecard WHERE id=?",id);
	}
	/**
	 *修改商品
	 */
	public boolean updategood(GoodsEcard goods){
		return goods.update();
	}
	/**
	 * 根据商品名字获取商品ID  按时间排序倒序第一个
	 * @param name
	 * @return
	 */
	public int findidByName(String name){
		return GoodsEcard.me().findFirst("SELECT * FROM  t_bus_goods_ecard WHERE name=? order by createDate desc",name).get("id");
	}
	/**
	 * 增加商品
	 */
	public boolean add(GoodsEcard goods){
		return goods.save();
	}
	/**
	 * 轮播图片排序
	 * @param sortMap
	 * @return
	 */
	public boolean sortImages(Map<String,String> sortMap){
		int batchSize = sortMap.size();
		List<String> sqlList = new ArrayList<String>();
		for(String id : sortMap.keySet()){
			sqlList.add("UPDATE t_bus_goods_base_images SET orderNo="+sortMap.get(id)+" WHERE id="+id);
		}
		int[] result = Db.batch(sqlList,batchSize);
		return result.length>0;
	}
	
	/**
	 * 详情图片排序
	 * @param sortMap
	 * @return
	 */
	public boolean sortDetailImages(Map<String,String> sortMap){
		int batchSize = sortMap.size();
		List<String> sqlList = new ArrayList<String>();
		for(String id : sortMap.keySet()){
			sqlList.add("UPDATE goods_details_images SET orderNo="+sortMap.get(id)+" WHERE id="+id);
		}
		int[] result = Db.batch(sqlList,batchSize);
		return result.length>0;
	}
	
	/**
	 * 商品图片列表
	 * 
	 * @return
	 */
	public List<GoodsBaseImages> findAllImages(String LunBoNum){
		return GoodsBaseImages.me().find("SELECT A.* FROM t_bus_goods_base_images A WHERE A.LunBoNum=? ORDER BY A.orderNo",LunBoNum);
	}
	
	/**
	 * 商品详情图片列表
	 * 
	 * @return
	 */
	public List<DetailsImg> findDetailImages(String LunBoNum){
		return DetailsImg.me().find("SELECT A.* FROM goods_details_images A WHERE A.LunBoNum=? ORDER BY A.orderNo",LunBoNum);
	}
	
	
	/**
	 * 添加图片
	 * @param newObject
	 * @return
	 */
	public boolean addImage(GoodsBaseImages newObject){
		return newObject.save();
	}
	
	
	/**
	 * 添加详情图片
	 * @param newObject
	 * @return
	 */
	public boolean addDetailImage(DetailsImg newObject){
		return newObject.save();
	}
	
	
	/**
	 * 删除图片
	 * @param id
	 * @return
	 */
	public boolean delImage(int id){
		return GoodsBaseImages.me().deleteById(id);
	}
	
	
	/**
	 * 删除详情图片
	 * @param id
	 * @return
	 */
	public boolean delDetailImage(int id){
		return DetailsImg.me().deleteById(id);
	}
	
	
	/**
	 * 设置图片为商品logo
	 * @param id
	 * @return
	 */
	@Before(Tx.class)
	public boolean setImageLogo(int goodsid,int id){
		List<String> sqlList = new ArrayList<String>();
		//更新该商品所有LOGO的isLogo标识为0
		sqlList.add("UPDATE t_bus_goods_base_images SET isLogo=0 WHERE goodsid="+goodsid);
		//更新当前LOGO的isLogo标识为1
		sqlList.add("UPDATE t_bus_goods_base_images SET isLogo=1 WHERE goodsid="+goodsid+" AND id="+id);
		//更新该商品的LOGO字段值为当前LOGO的地址
		sqlList.add("UPDATE t_bus_goods_base SET thumb=(SELECT thumb FROM t_bus_goods_base_images WHERE id="+id+") WHERE id="+goodsid);
		Db.batch(sqlList, sqlList.size());
		return true;
	}
	/**
	 * 
	 * 自定义参数存储
	 * @param option
	 * @return
	 */
	public boolean saveItem(GoodsSaleItem goodsSaleItem){
		return goodsSaleItem.save();
	}
	/**
	 * 删除当前商品对应的扩展信息
	 * @param goodId
	 * @return
	 */
	public int deleteitem(int goodsid) {
		return Db.update("DELETE FROM t_bus_goods_sale_items WHERE goodsid=?",goodsid);
	}
	
	/**
	 * 根据商品id查询所有的扩展信息
	 * @param iid
	 * @return
	 */
	public List<GoodsItem> findAllItems(int goodsid){
		return GoodsItem.me().find("SELECT * FROM  t_bus_goods_sale_items  WHERE goodsid=?",goodsid);
	}
	
	/**
	 * 查询对应的二级category
	 * @param iid
	 * @return
	 */
	public List<Record> finTwoCategory(String categoryonid) {
		return Db.find("select categoryId,categoryName from t_dict_category where categoryFatherId in ('"+categoryonid+"')");
	}
	/**
	 * 查询对应的三级category
	 * @param iid
	 * @return
	 */
	public List<Record> finThreeCategory(String categorytwid) {
		return Db.find("select categoryId,categoryName from t_dict_category where categoryFatherId in ('"+categorytwid+"')");
	}
	/**
	 * 查询对应的四级category
	 * @param iid
	 * @return
	 */
	public List<Record> finFourCategory(String categorythid) {
		return Db.find("select categoryId,categoryName from t_dict_category where categoryFatherId in ('"+categorythid+"')");
	}
	/**
	 * 查询所有的模板信息
	 */
	public List<GoodsSaleTemplate> findAllGoodsTemplates(){
		return GoodsSaleTemplate.me().find("SELECT * FROM t_bus_goods_sale_template");
	}
	/**
	 * 存储商品模板关联信息
	 */
	public boolean savegt(GoodsSaleGT goods){
		return goods.save();
	}
	/**
	 * 查询当前商品是否已关联模板
	 */
	public GoodsSaleGT findTemBygoodsid(int goodsid){
		return GoodsSaleGT.me().findFirst("SELECT * FROM t_bus_goods_sale_gt WHERE goodsid=?" ,goodsid);
	}
	/**
	 * 根据模板id查询模板信息
	 * @param tid
	 * @return
	 */
	public GoodsSaleTemplate findTemplateById(int tid) {
		// TODO Auto-generated method stub
		return GoodsSaleTemplate.me().findFirst("SELECT * FROM t_bus_goods_sale_template WHERE id = ?",tid);
	}
	/**
	 * 根据规格id查询对应option
	 * @param specid
	 * @param id 
	 * @return 
	 */
	/*public  List<Option> finOptionById(int specid, int id) {
		// TODO Auto-generated method stub
		return Option.me().find("SELECT id FROM t_bus_goods_sale_option WHERE specid = ?",specid).get(id);
	}*/
	public List<GoodsSaleOption> finOptionById(Integer specid){
		return GoodsSaleOption.me().find("SELECT * FROM  t_bus_goods_sale_option o WHERE specid=?",specid);
	}
	/**
	 * 根据id查询规格名称
	 * @param iid
	 * @return
	 */
	public GoodsSaleSpec findSpecByid(int specid) {
		// TODO Auto-generated method stub
		return GoodsSaleSpec.me().findFirst("SELECT * FROM t_bus_goods_sale_spec WHERE id = ?",specid);
	}
	/**
	 * 根据id查询参数名称
	 * @param id
	 * @return
	 */
	public String findOptionNameByid(int id){
		return Category.me().findFirst("SELECT o.optionName FROM  t_bus_goods_sale_option o WHERE id=?",id).get("optionName");
	}	
	
}